package zrar.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

import zrar.entity.AnswerPeople;
import zrar.entity.Prize;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtil {

	static List<AnswerPeople> peoples = new ArrayList<AnswerPeople>();

	public static List<AnswerPeople> readExcelFile(String filename) {
		File file = new File(filename);
		return readExcelFile(file);
	}

	// 读取 数据
	public static List<AnswerPeople> readExcelFile(File file) {
		try {
			InputStream is = new FileInputStream(file);
			Workbook workbook = Workbook.getWorkbook(is);

			// 获取第一张Sheet表
			Sheet sheet = (Sheet) workbook.getSheet(0);

			int row = sheet.getRows();
			int col = sheet.getColumns();

			Cell c = null;

			if (peoples.size() > 0) {
				peoples.clear();
			}

			for (int i = 1; i < row; i++) {
				AnswerPeople people = new AnswerPeople();
				for (int j = 0; j < col; j++) {
					c = sheet.getCell(j, i);
					String content = c.getContents();
					putInPeople(people, content, j);
				}
				peoples.add(people);
			}

			

			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return peoples;
	}

	private static void putInPeople(AnswerPeople people, String content, int j) {

		switch (j) {
		case 0:
			people.setId(content);
			break;
		case 1:
			people.setPaperId(content);
			break;
		case 2:
			people.setPeopleName(content);
			break;
		case 3:
			people.setPeoCorpType(content);
			break;
		case 4:
			people.setPeoTel(content);
			break;
		case 5:
			people.setMailNo(content);
			break;
		case 6:
			people.setAddress(content);
			break;
		case 7:
			people.setCorrectCount(Integer.parseInt(content));
			break;
		default:
			System.out.println(" default ");
		}

	}

	// 写入excel 文件
	public static void writeExcel(File file) throws Exception {
		List<Prize> prizes = SystemUtil.getPrizelist();
		jxl.write.WritableWorkbook book = null;
		
			book = jxl.Workbook.createWorkbook(file);

			jxl.write.WritableSheet sheet = book.createSheet("sheet1", 0);
			
			
			sheet.setColumnView(1, 18);// 设置单元格的宽度
			sheet.setColumnView(2, 18);
			sheet.setColumnView(3, 18);
			sheet.setColumnView(4, 40);
			
			
			List<String> reslist=SystemUtil.getResultPrizes();
			for(String pname:reslist){	
				Prize priz=SystemUtil.findPrizeByName(pname);
				inputExcel(sheet,priz , getStartRow(pname));
				
			}
			
			book.write();
			book.close();
		
	}

	private static int getStartRow(String pname) {
		//List<Prize> prizes = SystemUtil.getPrizelist();
		List<String> reslist=SystemUtil.getResultPrizes();
		
		int startRow = 0;
		int index = reslist.indexOf(pname);
		
		for(int j=0;j<index;j++){
			Prize prize=SystemUtil.findPrizeByName(reslist.get(j));
			startRow = startRow +prize.getPersons().size()+3;
		}
		
		return startRow;
	}

	public static void inputExcel(WritableSheet sheet, Prize prize, int startRow)
			throws RowsExceededException, WriteException {
		// 奖项名字
		Label prizeName = new Label(0, startRow, prize.getPname());
		sheet.addCell(prizeName);

		// 序号 答题编号 姓名 手机号码 地 址
		Label tableTitle1 = new Label(0, startRow + 1, "序号");
		sheet.addCell(tableTitle1);
		
		Label tableTitle2 = new Label(1, startRow + 1, "姓名");
		sheet.addCell(tableTitle2);
		Label tableTitle3 = new Label(2, startRow + 1, "手机号码");
		sheet.addCell(tableTitle3);
		Label tableTitle4 = new Label(3, startRow + 1, "邮编");
		sheet.addCell(tableTitle4);
		Label tableTitle5 = new Label(4, startRow + 1, "地   址");
		sheet.addCell(tableTitle5);

		List<AnswerPeople> prizePeoples = prize.getPersons();

		for (int row = 0; row < prizePeoples.size(); row++) {
			AnswerPeople peo = prizePeoples.get(row);
			for (int col = 0; col < 5; col++) {
				Label cell = new Label(col, startRow + 2 + row, getFromPeople(
						peo, row, col));
				sheet.addCell(cell);
			}

		}
	}

	public static String getFromPeople(AnswerPeople people, int row, int col) {
		String content = "";
		switch (col) {
		case 0:
			content = (row + 1) + "";
			break;
		case 1:
			content = people.getPeopleName();
			break;
		case 2:
			content = people.getPeoTel();
			break;
		case 3:
			content = people.getMailNo();
			break;
		case 4:
			content = people.getAddress();
			break;
		}
		return content;
	}

	public static void writeTXT(File file) {
		// 写入文件使用FileOutputStream,在这个范例中,写入文件的方式是将之前内容清除并重新写入.若想把新增的内容加在原有的文件内容后面，可以使用FileOutputStream(String
		// name,Boolean append)这个构造函数.
		BufferedWriter out = null;
		if (file != null) {
			try {
				out = new BufferedWriter(new OutputStreamWriter(
						new FileOutputStream(file, false), "UTF-8"));
			} catch (FileNotFoundException fe) {
				return;
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			String content = getResultContent();
			try {
				out.write(content);
			} catch (IOException ioe) {

			} finally {
				try {
					if (out != null)
						out.close();
				} catch (IOException ioe2) {
				}
			}
		}
	}

	private static String getResultContent() {
		StringBuffer content = new StringBuffer();
		content.append("\r\n");
		content.append("生成以下数据的时间：" + SystemUtil.getNowTime() + "\r\n");
		
		List<String> reslist=SystemUtil.getResultPrizes();
		for(String pname:reslist){			
			buildTxt(SystemUtil.findPrizeByName(pname),content);
		}
		
		content.append("\r\n");
		content.append("\r\n");
		return content.toString();
	}
	
	private static void buildTxt(Prize priz,StringBuffer content){
		List<AnswerPeople> peoples =null;
		AnswerPeople people = null;
		peoples = priz.getPersons();
		if (peoples.size() > 0) {
			content.append("\r\n");
			content.append("\r\n");
			content.append(priz.getPname() + "\r\n");
			content.append("序号\t姓名\t\t手机号码\t邮编\t地 址" + "\r\n");

			for (int j = 0; j < peoples.size(); j++) {
				people = peoples.get(j);
				content.append((j + 1));
				content.append("\t" + people.getPeopleName());
				content.append("\t\t" + people.getPeoTel());
				content.append("\t" + people.getMailNo());
				content.append("\t" + people.getAddress() + "\r\n");
			}
		}
	}

}
